﻿Public Class CWaliKelas
    Inherits CContext

    Private SELECT_COMMAND As String
    Private DELETE_COMMAND As String
    Private INSERT_COMMAND As String

    Public Function getByIdSemester(ByVal idsemester As Integer) As DataTable
        SELECT_COMMAND = "select k.id as id_kls, k.nama, wk.id_guru, g.nama as nm_gr,  wk.id_smstr, s.nama as nm_smstr from tbl_kelas as k left join ( select * from tbl_wali_kelas as tw where tw.id_smstr =" & idsemester & ") as  wk on wk.id_kls = k.id  left join tbl_guru g on wk.id_guru = g.id left join tbl_semester s on wk.id_smstr = s.id "

        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Sub DeleteByIdSemester(ByVal idSemester As Integer)
        DELETE_COMMAND = "DELTE FROM TBL_WALI_KELAS WHERE ID_SMTR=" & idSemester
        MyBase.DeleteData(DELETE_COMMAND)
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="idSemester"></param>
    ''' <param name="dataWali">isi table : id_kls,id_guru,id_smstr</param>
    ''' <remarks></remarks>
    Public Sub Rubah(ByVal idSemester As Integer, ByVal dataWali As DataTable)
        'hapus by idSemester
        DeleteByIdSemester(idSemester)

        For Each aRow As DataRow In dataWali.Rows
            If aRow.Item("id_guru") <> "" Then
                INSERT_COMMAND = "INSERT INTO TBL_WALI_KELAS(ID_KLS,ID_GURU,ID_SMSTR) VALUES (" & aRow.Item("ID_KLS") & "," & aRow.Item("ID_GURU") & "," & aRow.Item("ID_SMSTR") & ")"
                MyBase.InsertData(INSERT_COMMAND)
            End If
        Next
    End Sub

    Public Sub RubahTSql(ByVal idSemester As Integer, ByVal dataWali As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try


            DELETE_COMMAND = "DELETE FROM TBL_WALI_KELAS WHERE ID_SMSTR=" & idSemester
            Dim MYSQL_DELETE_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_DELETE_CMD
                .CommandText = DELETE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            Dim MYSQL_INSERT_CMD As MySql.Data.MySqlClient.MySqlCommand
            For Each aRow As DataRow In dataWali.Rows
                If Not IsDBNull(aRow.Item("id_guru")) Or aRow.Item("id_guru").ToString.Trim <> "" Then
                    MYSQL_INSERT_CMD = MyBase.MySqlConnect.CreateCommand
                    With MYSQL_INSERT_CMD
                        .CommandText = "INSERT INTO TBL_WALI_KELAS(ID_KLS,ID_GURU,ID_SMSTR) VALUES (" & aRow.Item("ID_KLS") & "," & aRow.Item("ID_GURU") & "," & idSemester & ")"
                        .Transaction = _TMysql
                        .ExecuteNonQuery()
                    End With
                End If
            Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try

    End Sub

End Class
